Public Class Class_CheckCarNotCloseWork
    Function CheckCarNotCloseWork( _
                                    ByVal UserID As String, _
                                    ByVal TmpStartDate As String, _
                                    ByVal StartDate As String, _
                                    ByVal TmpStopDate As String, _
                                    ByVal StopDate As String, _
                                    ByVal cOnnectionString As String) As DataSet
        Dim CLS As New Class_Connection
        Dim StrSql As String
        Dim Conn As New OleDb.OleDbConnection
        Dim DsRet As New DataSet

        StrSql = StrSql & " Select "
        StrSql = StrSql & " 	StartDay=	("
        StrSql = StrSql & " 			SELECT "
        StrSql = StrSql & " 				COUNT(*) As CountDayStart"
        StrSql = StrSql & " 			FROM"
        StrSql = StrSql & " 				("
        StrSql = StrSql & " 					SELECT"
        StrSql = StrSql & " 						DisTinct"
        StrSql = StrSql & " 						InvReserveInfo.UserId,"
        StrSql = StrSql & " 						TmpReserveDay = Case "
        StrSql = StrSql & " 						When"
        StrSql = StrSql & " 							Convert(Int,Substring(InvReserveDataTime.ReserveDay,7,4)) > 2400"
        StrSql = StrSql & " 						Then"
        StrSql = StrSql & " 							Convert(Varchar,Convert(Int,Substring(InvReserveDataTime.ReserveDay,7,4)) - 543 ) + '-' +"
        StrSql = StrSql & " 							Substring(InvReserveDataTime.ReserveDay,4,2) + '-' +"
        StrSql = StrSql & " 							Substring(InvReserveDataTime.ReserveDay,1,2) 		"
        StrSql = StrSql & " 						Else"
        StrSql = StrSql & " 							Substring(InvReserveDataTime.ReserveDay,7,4) + '-' +"
        StrSql = StrSql & " 							Substring(InvReserveDataTime.ReserveDay,4,2) + '-' +"
        StrSql = StrSql & " 							Substring(InvReserveDataTime.ReserveDay,1,2) "
        StrSql = StrSql & " 						End "
        StrSql = StrSql & "                     FROM         "
        StrSql = StrSql & " 		                InvReserveInfo INNER JOIN"
        StrSql = StrSql & " 		                InvCarType ON InvReserveInfo.TypeId = InvCarType.TypeID LEFT OUTER JOIN"
        StrSql = StrSql & " 		                InvReserveDataTime ON InvReserveInfo.ReserveId = InvReserveDataTime.ReserveId LEFT OUTER JOIN"
        StrSql = StrSql & " 		                InvReserveDataCarDriver ON InvReserveInfo.ReserveId = InvReserveDataCarDriver.ReserveId LEFT OUTER JOIN"
        StrSql = StrSql & " 		                InvReserveTimeClostWork ON InvReserveDataCarDriver.BdNumber = InvReserveTimeClostWork.BdNumber"
        StrSql = StrSql & " 					Where "
        StrSql = StrSql & " 						(InvReserveInfo.UserId = '" & UserID & "') 	AND "
        StrSql = StrSql & " 	                    (ISNULL(InvReserveDataCarDriver.CostWorkStatus,9) <> 1) AND"
        StrSql = StrSql & " 	                    (InvReserveInfo.ApproveBdStatus in ('BK',''))"
        StrSql = StrSql & " 				) TMPStart"
        StrSql = StrSql & " 			"
        StrSql = StrSql & " 			Where "
        StrSql = StrSql & " 				(TMPStart.TmpReserveDay  Between '" & TmpStartDate & "' And '" & StartDate & "')"
        StrSql = StrSql & " 		) "
        StrSql = StrSql & " ,"
        StrSql = StrSql & " 	StopDay=	("
        StrSql = StrSql & " 			SELECT "
        StrSql = StrSql & " 				COUNT(*) As CountDayStop"
        StrSql = StrSql & " 			FROM"
        StrSql = StrSql & " 				("
        StrSql = StrSql & " 					SELECT"
        StrSql = StrSql & " 						DisTinct"
        StrSql = StrSql & " 						InvReserveInfo.UserId,"
        StrSql = StrSql & " 						TmpReserveDay = Case "
        StrSql = StrSql & " 						When"
        StrSql = StrSql & " 							Convert(Int,Substring(InvReserveDataTime.ReserveDay,7,4)) > 2400"
        StrSql = StrSql & " 						Then"
        StrSql = StrSql & " 							Convert(Varchar,Convert(Int,Substring(InvReserveDataTime.ReserveDay,7,4)) - 543 ) + '-' +"
        StrSql = StrSql & " 							Substring(InvReserveDataTime.ReserveDay,4,2) + '-' +"
        StrSql = StrSql & " 							Substring(InvReserveDataTime.ReserveDay,1,2) 		"
        StrSql = StrSql & " 						Else"
        StrSql = StrSql & " 							Substring(InvReserveDataTime.ReserveDay,7,4) + '-' +"
        StrSql = StrSql & " 							Substring(InvReserveDataTime.ReserveDay,4,2) + '-' +"
        StrSql = StrSql & " 							Substring(InvReserveDataTime.ReserveDay,1,2) "
        StrSql = StrSql & " 						End  "
        StrSql = StrSql & " 						"
        StrSql = StrSql & "                     FROM         "
        StrSql = StrSql & " 		                InvReserveInfo INNER JOIN"
        StrSql = StrSql & " 		                InvCarType ON InvReserveInfo.TypeId = InvCarType.TypeID LEFT OUTER JOIN"
        StrSql = StrSql & " 		                InvReserveDataTime ON InvReserveInfo.ReserveId = InvReserveDataTime.ReserveId LEFT OUTER JOIN"
        StrSql = StrSql & " 		                InvReserveDataCarDriver ON InvReserveInfo.ReserveId = InvReserveDataCarDriver.ReserveId LEFT OUTER JOIN"
        StrSql = StrSql & " 		                InvReserveTimeClostWork ON InvReserveDataCarDriver.BdNumber = InvReserveTimeClostWork.BdNumber"
        StrSql = StrSql & " 					Where "
        StrSql = StrSql & " 						(InvReserveInfo.UserId ='" & UserID & "') AND 	"
        StrSql = StrSql & " 	                    (ISNULL(InvReserveDataCarDriver.CostWorkStatus,9) <> 1) AND"
        StrSql = StrSql & " 	                    (InvReserveInfo.ApproveBdStatus in ('BK',''))"
        StrSql = StrSql & " 				) TMPStop"
        StrSql = StrSql & " 			"
        StrSql = StrSql & " 			Where "
        StrSql = StrSql & " 				(TMPStop.TmpReserveDay  Between '" & StopDate & "' And '" & TmpStopDate & "')"
        StrSql = StrSql & " 		) "


        Conn = CLS.ConnectionOpen(cOnnectionString)
        DsRet = CLS.BindData(Conn, , , StrSql)
        CLS.ConnectionClose(Conn)

        CheckCarNotCloseWork = DsRet


    End Function

    Function Show_CarReserveNotCloseWork( _
                                            ByVal UserID As String, _
                                            ByVal TmpStartDate As String, _
                                            ByVal StartDate As String, _
                                            ByVal TmpStopDate As String, _
                                            ByVal StopDate As String, _
                                            ByVal cOnnectionString As String) As DataSet
        Dim CLS As New Class_Connection
        Dim StrSql As String
        Dim Conn As New OleDb.OleDbConnection
        Dim DsRet As New DataSet

        StrSql = StrSql & " Select  Distinct"
        StrSql = StrSql & " 	ReserveId,"
        StrSql = StrSql & " 	BdNumber,"
        StrSql = StrSql & " 	ReserveDate,"
        StrSql = StrSql & " 	UserId,"
        StrSql = StrSql & " 	UserName,"
        StrSql = StrSql & " 	StartDate,"
        StrSql = StrSql & " 	StopDate,"
        StrSql = StrSql & " 	TmpDate,"
        StrSql = StrSql & " 	ApproveBdStatus,"
        StrSql = StrSql & " 	CostWorkStatus,"
        StrSql = StrSql & " 	DataTypeId,"
        StrSql = StrSql & " 	TypeId,"
        StrSql = StrSql & " 	TypeName,"
        StrSql = StrSql & " 	Area"
        StrSql = StrSql & " From"
        StrSql = StrSql & " 	("
        StrSql = StrSql & " 	SELECT     "
        StrSql = StrSql & "          	InvReserveInfo.ReserveId, "
        StrSql = StrSql & "          	ISNULL(InvReserveDataCarDriver.BdNumber,'') As BdNumber ,"
        StrSql = StrSql & "          	InvReserveInfo.ReserveDate, "
        StrSql = StrSql & "          	InvReserveInfo.UserId, "
        StrSql = StrSql & "          	'' AS  UserName, "
        StrSql = StrSql & "          	InvReserveInfo.StartDate , "
        StrSql = StrSql & "          	InvReserveInfo.StopDate ,"
        StrSql = StrSql & " 		    TmpReserveDay = Case "
        StrSql = StrSql & " 				When"
        StrSql = StrSql & " 					Convert(Int,Substring(InvReserveDataTime.ReserveDay,7,4)) > 2400"
        StrSql = StrSql & " 				Then"
        StrSql = StrSql & " 					Convert(Varchar,Convert(Int,Substring(InvReserveDataTime.ReserveDay,7,4)) - 543 ) + '-' +"
        StrSql = StrSql & " 					Substring(InvReserveDataTime.ReserveDay,4,2) + '-' +"
        StrSql = StrSql & " 					Substring(InvReserveDataTime.ReserveDay,1,2) 		"
        StrSql = StrSql & " 				Else"
        StrSql = StrSql & " 					Substring(InvReserveDataTime.ReserveDay,7,4) + '-' +"
        StrSql = StrSql & " 					Substring(InvReserveDataTime.ReserveDay,4,2) + '-' +"
        StrSql = StrSql & " 					Substring(InvReserveDataTime.ReserveDay,1,2) "
        StrSql = StrSql & " 				End ,"
        StrSql = StrSql & "          	'' AS TmpDate , "
        StrSql = StrSql & "          	InvReserveInfo.Area,"
        StrSql = StrSql & "          	InvReserveInfo.ApproveBdStatus, "
        StrSql = StrSql & "          	ISNULL(InvReserveDataCarDriver.CostWorkStatus,9) As CostWorkStatus,"
        StrSql = StrSql & " 		    InvReserveInfo.DataTypeId, "
        StrSql = StrSql & " 		    InvReserveInfo.TypeId, "
        StrSql = StrSql & " 		    InvCarType.TypeName"
        StrSql = StrSql & "     FROM         "
        StrSql = StrSql & " 		InvReserveInfo INNER JOIN"
        StrSql = StrSql & " 		InvCarType ON InvReserveInfo.TypeId = InvCarType.TypeID LEFT OUTER JOIN"
        StrSql = StrSql & " 		InvReserveDataTime ON InvReserveInfo.ReserveId = InvReserveDataTime.ReserveId LEFT OUTER JOIN"
        StrSql = StrSql & " 		InvReserveDataCarDriver ON InvReserveInfo.ReserveId = InvReserveDataCarDriver.ReserveId LEFT OUTER JOIN"
        StrSql = StrSql & " 		InvReserveTimeClostWork ON InvReserveDataCarDriver.BdNumber = InvReserveTimeClostWork.BdNumber"
        StrSql = StrSql & "     Where "
        StrSql = StrSql & "         (ISNULL(InvReserveDataCarDriver.CostWorkStatus,9) <> 1) AND"
        StrSql = StrSql & "         (InvReserveInfo.ApproveBdStatus in ('BK',''))AND"
        StrSql = StrSql & " 		(InvReserveInfo.UserId ='" & UserID & "') 	"
        StrSql = StrSql & " 	) TmpCarReserve"
        StrSql = StrSql & " Where"
        StrSql = StrSql & " 	(TmpCarReserve.TmpReserveDay  Between '" & TmpStartDate & "' And '" & StartDate & "')"
        StrSql = StrSql & " Union"
        StrSql = StrSql & " Select Distinct"
        StrSql = StrSql & " 	ReserveId,"
        StrSql = StrSql & " 	BdNumber,"
        StrSql = StrSql & " 	ReserveDate,"
        StrSql = StrSql & " 	UserId,"
        StrSql = StrSql & " 	UserName,"
        StrSql = StrSql & " 	StartDate,"
        StrSql = StrSql & " 	StopDate,"
        StrSql = StrSql & " 	TmpDate,"
        StrSql = StrSql & " 	ApproveBdStatus,"
        StrSql = StrSql & " 	CostWorkStatus,"
        StrSql = StrSql & " 	DataTypeId,"
        StrSql = StrSql & " 	TypeId,"
        StrSql = StrSql & " 	TypeName,"
        StrSql = StrSql & " 	Area"
        StrSql = StrSql & " From"
        StrSql = StrSql & " 	("
        StrSql = StrSql & " 	SELECT     "
        StrSql = StrSql & "          	InvReserveInfo.ReserveId, "
        StrSql = StrSql & "          	ISNULL(InvReserveDataCarDriver.BdNumber,'') As BdNumber ,"
        StrSql = StrSql & "          	InvReserveInfo.ReserveDate, "
        StrSql = StrSql & "          	InvReserveInfo.UserId, "
        StrSql = StrSql & "          	'' AS  UserName, "
        StrSql = StrSql & "          	InvReserveInfo.StartDate , "
        StrSql = StrSql & "          	InvReserveInfo.StopDate ,"
        StrSql = StrSql & " 		TmpReserveDay = Case "
        StrSql = StrSql & " 				When"
        StrSql = StrSql & " 					Convert(Int,Substring(InvReserveDataTime.ReserveDay,7,4)) > 2400"
        StrSql = StrSql & " 				Then"
        StrSql = StrSql & " 					Convert(Varchar,Convert(Int,Substring(InvReserveDataTime.ReserveDay,7,4)) - 543 ) + '-' +"
        StrSql = StrSql & " 					Substring(InvReserveDataTime.ReserveDay,4,2) + '-' +"
        StrSql = StrSql & " 					Substring(InvReserveDataTime.ReserveDay,1,2) 		"
        StrSql = StrSql & " 				Else"
        StrSql = StrSql & " 					Substring(InvReserveDataTime.ReserveDay,7,4) + '-' +"
        StrSql = StrSql & " 					Substring(InvReserveDataTime.ReserveDay,4,2) + '-' +"
        StrSql = StrSql & " 					Substring(InvReserveDataTime.ReserveDay,1,2) "
        StrSql = StrSql & " 				End ,"
        StrSql = StrSql & "          	'' AS TmpDate , "
        StrSql = StrSql & "          	InvReserveInfo.Area,"
        StrSql = StrSql & "          	InvReserveInfo.ApproveBdStatus, "
        StrSql = StrSql & "          	ISNULL(InvReserveDataCarDriver.CostWorkStatus,9) As CostWorkStatus,"
        StrSql = StrSql & "    		    InvReserveInfo.DataTypeId, "
        StrSql = StrSql & " 		    InvReserveInfo.TypeId, "
        StrSql = StrSql & " 		    InvCarType.TypeName"
        StrSql = StrSql & "     FROM         "
        StrSql = StrSql & " 		InvReserveInfo INNER JOIN"
        StrSql = StrSql & " 		InvCarType ON InvReserveInfo.TypeId = InvCarType.TypeID LEFT OUTER JOIN"
        StrSql = StrSql & " 		InvReserveDataTime ON InvReserveInfo.ReserveId = InvReserveDataTime.ReserveId LEFT OUTER JOIN"
        StrSql = StrSql & " 		InvReserveDataCarDriver ON InvReserveInfo.ReserveId = InvReserveDataCarDriver.ReserveId LEFT OUTER JOIN"
        StrSql = StrSql & " 		InvReserveTimeClostWork ON InvReserveDataCarDriver.BdNumber = InvReserveTimeClostWork.BdNumber"
        StrSql = StrSql & "     Where "
        StrSql = StrSql & "         (ISNULL(InvReserveDataCarDriver.CostWorkStatus,9) <> 1) AND"
        StrSql = StrSql & "         (InvReserveInfo.ApproveBdStatus in ('BK',''))AND"
        StrSql = StrSql & " 		(InvReserveInfo.UserId ='" & UserID & "') 	"
        StrSql = StrSql & " 	) TmpCarReserve"
        StrSql = StrSql & " Where"
        StrSql = StrSql & " 	(TmpCarReserve.TmpReserveDay  Between '" & StopDate & "' And '" & TmpStopDate & "')"

        Conn = CLS.ConnectionOpen(cOnnectionString)
        DsRet = CLS.BindData(Conn, , , StrSql)
        CLS.ConnectionClose(Conn)

        Show_CarReserveNotCloseWork = DsRet

    End Function




End Class
